var fs = require('fs');
var _ = require('underscore');

var HrStopwatch = require('./utils/hr-stopwatch');

var ExcelJS = require('../excel');

var Workbook = ExcelJS.Workbook;

var filename = process.argv[2];

var wb = new Workbook();
var ws = wb.addWorksheet('blort');

var fonts = {
  arialBlackUI14: { name: 'Arial Black', family: 2, size: 14, underline: true, italic: true },
  comicSansUdB16: { name: 'Comic Sans MS', family: 4, size: 16, underline: 'double', bold: true },
  whiteText: { name: 'Arial Black', family: 2, size: 14, color: { argb: 'FFFFFFFF' }},
};

var alignments = [
  { text: 'Top Left', alignment: { horizontal: 'left', vertical: 'top' } },
  { text: 'Middle Centre', alignment: { horizontal: 'center', vertical: 'middle' } },
  { text: 'Bottom Right', alignment: { horizontal: 'right', vertical: 'bottom' } },
  { text: 'Wrap Text - Wrapping Wrapping Wrappity Wrap Wrap Wrap', alignment: { wrapText: true } },
  { text: 'Indent 1', alignment: { indent: 1 } },
  { text: 'Indent 2', alignment: { indent: 2 } },
  { text: 'Rotate 15', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 15 } },
  { text: 'Rotate 30', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 30 } },
  { text: 'Rotate 45', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 45 } },
  { text: 'Rotate 60', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 60 } },
  { text: 'Rotate 75', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 75 } },
  { text: 'Rotate 90', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 90 } },
  { text: 'Rotate -15', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -55 } },
  { text: 'Rotate -30', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -30 } },
  { text: 'Rotate -45', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -45 } },
  { text: 'Rotate -60', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -60 } },
  { text: 'Rotate -75', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -75 } },
  { text: 'Rotate -90', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: -90 } },
  { text: 'Vertical Text', alignment: { horizontal: 'right', vertical: 'bottom', textRotation: 'vertical' } },
];
// var badAlignments = [
//  { text: 'Rotate -91', alignment: { textRotation: -91 } },
//  { text: 'Rotate 91', alignment: { textRotation: 91 } },
//  { text: 'Indent -1', alignment: { indent: -1 } },
//  { text: 'Blank', alignment: {  } }
// ];

var borders = {
  thin: { top: {style:'thin'}, left: {style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'}},
  doubleRed: { color: {argb:'FFFF0000'}, top: {style:'double'}, left: {style:'double'}, bottom: {style:'double'}, right: {style:'double'}},
  thickRainbow: {
    top: {style:'double', color: {argb:'FFFF00FF'}},
    left: {style:'double', color: {argb:'FF00FFFF'}},
    bottom: {style:'double', color: {argb:'FF00FF00'}},
    right: {style:'double', color: {argb:'FFFF00FF'}},
    diagonal: {style:'double', color: {argb:'FFFFFF00'}, up: true, down: true},
  },
  thinWhite: { top: {style:'thin'}, left: {style:'thin'}, bottom: {style:'thin'}, right: {style:'thin'}, color: { argb: 'FFFFFFFF'}},
};

var fills = {
  solidGreen: {type: 'pattern', pattern:'solid', fgColor:{argb:'FF00FF00'}},
  redDarkVertical: {type: 'pattern', pattern:'darkVertical', fgColor:{argb:'FFFF0000'}},
  redGreenDarkTrellis: {type: 'pattern', pattern:'darkTrellis', fgColor:{argb:'FFFF0000'}, bgColor:{argb:'FF00FF00'}},
  blueWhiteHGrad: {type: 'gradient', gradient: 'angle', degree: 0,
    stops: [{position:0, color:{argb:'FF0000FF'}},{position:1, color:{argb:'FFFFFFFF'}}]},
  rgbPathGrad: {type: 'gradient', gradient: 'path', center:{left:0.5,top:0.5},
    stops: [{position:0, color:{argb:'FFFF0000'}},{position:0.5, color:{argb:'FF00FF00'}},{position:1, color:{argb:'FF0000FF'}}]},
};

ws.columns = [
  { header: 'Col 1', key:'key', width: 25 },
  { header: 'Col 2', key:'name', width: 32 },
  { header: 'Col 3', key:'age', width: 21 },
  { header: 'Col 4', key:'addr1', width: 18 },
  { header: 'Col 5', key:'addr2', width: 8 },
  { header: 'Col 6', width: 8 },
  { header: 'Col 7', width: 8 },
  { header: 'Col 8', width: 8, style: { font: fonts.comicSansUdB16, alignment: alignments[1].alignment } },
  { header: 'Col 9', width: 8, hidden: true }
];

ws.getCell('A2').value = 7;
ws.getCell('B2').value = 'Hello, World!';
ws.getCell('B2').font = fonts.comicSansUdB16;
ws.getCell('B2').border = borders.thin;

ws.getCell('C2').value = -5.55;
ws.getCell('C2').numFmt = '"£"#,##0.00;[Red]-"£"#,##0.00';
ws.getCell('C2').font = fonts.arialBlackUI14;

ws.getCell('D2').value = 3.14;
ws.getCell('D2').value = new Date();
ws.getCell('D2').numFmt = 'd-mmm-yyyy';
ws.getCell('D2').font = fonts.comicSansUdB16;
ws.getCell('D2').border = borders.doubleRed;

ws.getCell('E2').value = ['Hello', 'World'].join(', ') + '!';

ws.getCell('F2').value = true;
ws.getCell('G2').value = { error:  '#N/A'};
ws.getCell('H2').value = { error:  '#VALUE!'};

ws.getCell('A3').value = {text: 'www.google.com', hyperlink:'http://www.google.com'};
ws.getCell('A4').value = 'Boo!';
ws.getCell('C4').value = 'Hoo!';
ws.mergeCells('A4', 'C4');

ws.getCell('A5').value = 1;
ws.getCell('B5').value = 2;
ws.getCell('C5').value = {formula:'A5+B5', result:3};

ws.getCell('A6').value = 'Hello';
ws.getCell('B6').value = 'World';
ws.getCell('C6').value = {formula:'CONCATENATE(A6,", ",B6,"!")', result:'Hello, World!'};
ws.getCell('C6').border = borders.thickRainbow;

ws.getCell('A7').value = 1;
ws.getCell('B7').value = 2;
ws.getCell('C7').value = {formula:'A7+B7'};

var now = new Date();
ws.getCell('A8').value = now;
ws.getCell('B8').value = 0;
ws.getCell('C8').value = {formula:'A8+B8', result: now};

ws.getCell('A9').value = 1.6;
ws.getCell('A9').numFmt = '# ?/?';
ws.getCell('B9').value = 1.6;
ws.getCell('B9').numFmt = 'h:mm:ss';
ws.getCell('C9').value = 0.016;
ws.getCell('C9').numFmt = '0.00%';
ws.getCell('D9').value = 1.6;
ws.getCell('D9').numFmt = '[Green]#,##0 ;[Red](#,##0)';
ws.getCell('E9').value = 1.6;
ws.getCell('E9').numFmt = '#0.000';
ws.getCell('F9').value = 0.016;
ws.getCell('F9').numFmt = '# ?/?%';

ws.getCell('A10').value = '<';
ws.getCell('B10').value = '>';
ws.getCell('C10').value = '<a>';
ws.getCell('D10').value = '><';

ws.getRow(11).height = 40;
_.each(alignments, function(alignment, index) {
  var rowNumber = 11;
  var colNumber = index + 1;
  var cell = ws.getCell(rowNumber, colNumber);
  cell.value = alignment.text;
  cell.alignment = alignment.alignment;
});

var row12 = ws.getRow(12);
row12.height = 40;
row12.getCell(1).value = 'Blue White Horizontal Gradient';
row12.getCell(1).fill = fills.blueWhiteHGrad;
row12.getCell(2).value = 'Red Dark Vertical';
row12.getCell(2).fill = fills.redDarkVertical;
row12.getCell(3).value = 'Red Green Dark Trellis';
row12.getCell(3).fill = fills.redGreenDarkTrellis;
row12.getCell(4).value = 'RGB Path Gradient';
row12.getCell(4).fill = fills.rgbPathGrad;
row12.getCell(5).value = 'Solid Green';
row12.getCell(5).fill = fills.solidGreen;

// testing background and color trickery
ws.getCell('F5').value = 'white';
ws.getCell('F5').fill = fills.solidGreen;
ws.getCell('F5').border = borders.thinWhite;
ws.getCell('F5').font = fonts.whiteText;
ws.getCell('E4').fill = fills.solidGreen;
ws.getCell('E5').fill = fills.solidGreen;
ws.getCell('E6').fill = fills.solidGreen;
ws.getCell('F4').fill = fills.solidGreen;
ws.getCell('F6').fill = fills.solidGreen;
ws.getCell('G4').fill = fills.solidGreen;
ws.getCell('G5').fill = fills.solidGreen;
ws.getCell('G6').fill = fills.solidGreen;


// row and column styles
ws.getRow(13).font = fonts.arialBlackUI14;
ws.getCell('H12').value = 'Foo';
ws.getCell('G13').value = 'Foo';
ws.getCell('H13').value = 'Bar';
ws.getCell('I13').value = 'Baz';
ws.getCell('H14').value = 'Baz';

// hidden stuff
ws.getRow(16).hidden = true;
ws.getCell('I15').value = 'You Can\'t See Me!';
ws.getCell('A16').value = 'You Can\'t See Me!';

var A18 = ws.getCell('A18');
A18.value = 'Wrap Text - Wrapping Wrapping Wrappity Wrap Wrap Wrap';
A18.alignment = { wrapText: true };

var A20 = ws.getCell('A20');
A20.value = 'Wrap Text - Wrapping Wrappity Wrap';
A20.alignment = { shrinkToFit: true };

ws.getCell('A2').name = 'Passe';
ws.getCell('B2').name = 'Passe';

ws.getCell('E2').name = 'Greet';
ws.getCell('A22').value = {formula:'E2'};

ws.getCell('A24').value = 'Choose';
ws.getCell('D24').value = 'Hewie';
ws.getCell('D24').name = 'Nephews';
ws.getCell('E24').value = 'Dewie';
ws.getCell('E24').name = 'Nephews';
ws.getCell('F24').value = 'Louie';
ws.getCell('F24').name = 'Nephews';
ws.getCell('B24').dataValidation = {
  type: 'list',
  allowBlank: true,
  formulae: ['Nephews']
};

var stopwatch = new HrStopwatch();
stopwatch.start();
wb.xlsx.writeFile(filename)
  .then(function() {
    var micros = stopwatch.microseconds;
    console.log('Done.');
    console.log('Time taken:', micros)
  });
// .catch(function(error) {
//    console.log(error.message);
// })
